const { pool } = require('../config/db');

// 创建用户表 - 支持抖音unionid字段
exports.createUserTable = async () => {
  const createTableQuery = `
    CREATE TABLE IF NOT EXISTS users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      openid VARCHAR(100) NOT NULL UNIQUE,
      unionid VARCHAR(100) UNIQUE,
      nickname VARCHAR(50),
      avatar VARCHAR(255),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )
  `;
  
  try {
    // 先创建表（如果不存在）
    await pool.execute(createTableQuery);
    console.log('User table created or already exists!');
    
    // 检查unionid字段是否存在
    const [columns] = await pool.execute(
      `SELECT column_name FROM information_schema.columns 
       WHERE table_schema = ? AND table_name = 'users' AND column_name = 'unionid'`,
      [process.env.DB_NAME]
    );
    
    // 如果unionid字段不存在，则添加它
    if (columns.length === 0) {
      await pool.execute(
        `ALTER TABLE users 
         ADD COLUMN unionid VARCHAR(100) UNIQUE`
      );
      console.log('Added unionid column to users table!');
    } else {
      console.log('unionid column already exists in users table!');
    }
  } catch (error) {
    console.error('Error creating or updating user table:', error);
  }
};

// 根据openid查找用户
exports.findUserByOpenid = async (openid) => {
  const query = 'SELECT * FROM users WHERE openid = ?';
  const [rows] = await pool.execute(query, [openid]);
  return rows[0] || null;
};

// 根据ID查找用户
exports.findUserById = async (id) => {
  const query = 'SELECT * FROM users WHERE id = ?';
  const [rows] = await pool.execute(query, [id]);
  return rows[0] || null;
};

// 创建新用户 - 支持unionid字段
exports.createUser = async (userData) => {
  const { openid, unionid = '', nickname, avatar } = userData;
  const query = 'INSERT INTO users (openid, unionid, nickname, avatar) VALUES (?, ?, ?, ?)';
  const [result] = await pool.execute(query, [openid, unionid, nickname, avatar]);
  
  return {
    id: result.insertId,
    openid,
    unionid,
    nickname,
    avatar
  };
};

// 更新用户信息 - 支持unionid字段更新
exports.updateUser = async (openid, userData) => {
  const { nickname, avatar, unionid } = userData;
  
  // 构建更新字段和参数
  let updateFields = [];
  let params = [];
  
  if (nickname !== undefined) {
    updateFields.push('nickname = ?');
    params.push(nickname);
  }
  
  if (avatar !== undefined) {
    updateFields.push('avatar = ?');
    params.push(avatar);
  }
  
  if (unionid !== undefined) {
    updateFields.push('unionid = ?');
    params.push(unionid);
  }
  
  // 如果没有字段需要更新，直接返回用户信息
  if (updateFields.length === 0) {
    return this.findUserByOpenid(openid);
  }
  
  // 添加WHERE条件和参数
  const query = `UPDATE users SET ${updateFields.join(', ')} WHERE openid = ?`;
  params.push(openid);
  
  await pool.execute(query, params);
  
  return this.findUserByOpenid(openid);
};

// 根据用户ID查找用户
exports.findUserById = async (id) => {
  const query = 'SELECT * FROM users WHERE id = ?';
  const [rows] = await pool.execute(query, [id]);
  return rows[0] || null;
}